package graph;

import java.net.*;
import java.sql.*;
import java.util.HashMap;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.jgrapht.*;
import org.jgrapht.graph.*;


public final class LoadGraph
{

    private Connection laConnection;
    private Statement transmission;
    private ResultSet leResultat;
    private ResultSet resultatEducation;
    static HashMap<Long, User> hashmapUsers;
    static HashMap<Long, Group> hashmapGroups;
    static HashMap<Long, Location> hashmapLocation;
    static HashMap<Long, Event> hashmapEvent;
    static HashMap<Long, Work> hashmapWork;

    static HashMap<Long, Place> hashmapPlace;
    static HashMap<Long, GeoTag> hashmapGeoTag;
    static HashMap<Long, Like> hashmapLike;

    private UndirectedGraph<Object, DefaultEdge> g;
    
    

    public  LoadGraph(UndirectedGraph<Object, DefaultEdge> userGraph) {
        this.g = userGraph;
    }
    
    

    public void connect(){     
         /* Ici, obligation d’implémenter une structure de type try...catch pour nous
           * permettre de gérer les erreurs de Connection
           */
         try{
              // Pour se connecter à une base de donnée, il faut charger le driver
              // qui fait le lien entre le programme et la base de donnée
              Class.forName("com.mysql.jdbc.Driver");
              /* On charge et on configure le driver de la base de donnée à l’aide de la
                * classe DriverManager. On insert les informations de Connection ici
                * sql = le nom de ma base
                * root = l’utilisateur par défaut
                * "" = le mot de passe (ici j’en ai pas mis)
                */
              //laConnection = DriverManager.getConnection("jdbc:mysql://localhost/bdd","root","ba,ne9");
              laConnection = DriverManager.getConnection("jdbc:mysql://localhost/data_from_facebook","root","");
              transmission = laConnection.createStatement();
              
         }catch(Exception e){
              System.out.print("Impossible de se connecte à la base de données" + e.getMessage());
         }
    }
        
    public void addUsers(){
        try {        
            
            Long id_user;
            /* Exécution d’une requête : le ResultSet nous permet de parcourir les
             * informations retournées par la base de données dans le cas d’une sélection
             * de données
             */
            leResultat = transmission.executeQuery("select * from user");
            /* Pour afficher le resultat de la requête renvoyé par MySQL, il suffit de
             * bouclé sur notre ResultSet
            ResultSet.
             *
             * leResultat.getString("nom")     nom = champs de la table maTable
             */
            
            while (leResultat.next())
            {  
                id_user = leResultat.getLong("id_user");
               
                User u = new User(id_user, leResultat.getString("nom"), leResultat.getString("prenom"), leResultat.getString("sexe"), leResultat.getString("anniversaire"), leResultat.getString("update_date"), hashmapLocation.get(leResultat.getLong("id_location")));
 
                g.addVertex(u);
                
                hashmapUsers.put(id_user, u);
            }
                       
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
    public void addFriendLink(){
        try {     
            
             Long id_user, id_friend;
            /* Exécution d’une requête : le ResultSet nous permet de parcourir les
             * informations retournées par la base de données dans le cas d’une sélection
             * de données
             */
            leResultat = transmission.executeQuery("select * from est_ami");
            /* Pour afficher le resultat de la requête renvoyé par MySQL, il susuffit de
             * bouclé sur notre ResultSet
            ResultSet.
             *
             * leResultat.getString("nom")     nom = champs de la table maTable
             */
            
            while (leResultat.next()) {
                
                id_user = leResultat.getLong("id_user");
                id_friend = leResultat.getLong("id_friend");
                
                g.addEdge(hashmapUsers.get(id_user), hashmapUsers.get(id_friend));
                
            }
             
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void addGroupLink()
    {
        try {
            Long id_group;
            Long id_user;
            
            leResultat = transmission.executeQuery("select * from groupe");
            
            while (leResultat.next())
            {  
                id_group = leResultat.getLong("id_groupe");
               
                Group group = new Group(id_group, leResultat.getString("nom"));
 
                g.addVertex(group);
                
                hashmapGroups.put(id_group, group);
            }
             
             
            leResultat = transmission.executeQuery("select * from appartient");
            
            while (leResultat.next()) {
                
                id_user = leResultat.getLong("id_user");
                id_group = leResultat.getLong("id_groupe");
                
                g.addEdge(hashmapUsers.get(id_user), hashmapGroups.get(id_group));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
    public void addEventLink()
    {
        try {
            Long id_event;
            Long id_user;
            
            leResultat = transmission.executeQuery("select * from event");
            
            while (leResultat.next())
            {  
                id_event = leResultat.getLong("id_event");
               
                Event event = new Event(id_event, leResultat.getString("nom"));
 
                g.addVertex(event);
                
                 
                hashmapEvent.put(id_event, event);
            }
             
             
            leResultat = transmission.executeQuery("select * from participe");
            
            while (leResultat.next()) {
                
                id_user = leResultat.getLong("id_user");
                id_event = leResultat.getLong("id_event");
                
                g.addEdge(hashmapUsers.get(id_user), hashmapEvent.get(id_event));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
    public void addLocation()
    {
        try {
            Long id_location;
            
            leResultat = transmission.executeQuery("select * from location");
            
            while (leResultat.next())
            {  
                id_location = leResultat.getLong("id_location");
               
                Location loc = new Location(id_location, leResultat.getString("nom"));
                
                hashmapLocation.put(id_location, loc);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void addWork()
    {
        try {
            Long id_travail;
            Long id_user;
            
            leResultat = transmission.executeQuery("select * from travail");
            
            while (leResultat.next())
            {  
                id_travail = leResultat.getLong("id_travail");
               
                Work work = new Work(id_travail, leResultat.getString("nomEntreprise"));
 
                hashmapWork.put(id_travail, work);
            }
            
            
            leResultat = transmission.executeQuery("select * from travaille_dans");
            
            while (leResultat.next())
            {  
                id_user = leResultat.getLong("id_user");
                id_travail = leResultat.getLong("id_travail");
            
                hashmapUsers.get(id_user).addWork(hashmapWork.get(id_travail));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void addSchoolYear()
    {
        try {
            Long id_education;
            Long id_specialite;
            Long id_year_educ;
            Long id_user;
            
            String nomEcole;
            String nomSpecialite;
            int nom_year;
            
            leResultat = transmission.executeQuery("SELECT user.id_user as idU, education.nomEcole as nomE, specialite.nom_specialite as nomS, year.nom_year as nomY " +
                                                   "FROM user, etudie, education, specialite, year " +
                                                   "WHERE user.id_user = etudie.id_user " +
                                                   "AND etudie.id_education = education.id_education  " +
                                                   "AND etudie.id_specialite = specialite.id_specialite " +
                                                   "AND etudie.id_year_educ = year.id_year");
            
            while (leResultat.next())
            {  
                id_user = leResultat.getLong("idU");

                nomEcole = leResultat.getString("nomE");
                nomSpecialite = leResultat.getString("nomS");
                nom_year = leResultat.getInt("nomY");
                
                hashmapUsers.get(id_user).addSchoolYear(new SchoolYear(nomEcole, nomSpecialite, nom_year));

            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
            //ex.printStackTrace();
        }
    }
    
    public void addPlace(){

       try {           
            Long id_lieu;
            String nom;
            String rue;
            String ville;
            String pays;
            String zip_code;
            Long longitude;
            Long latitude;
         
            leResultat = transmission.executeQuery("select * from lieu");
            
            while (leResultat.next())
            {  
                id_lieu = leResultat.getLong("id_lieu");
                nom = leResultat.getString("nom");
                rue = leResultat.getString("rue");
                ville = leResultat.getString("ville");
                pays = leResultat.getString("pays");
                zip_code = leResultat.getString("zip_code");
                longitude = leResultat.getLong("longitude");
                latitude = leResultat.getLong("latitude");      
     
                Place place = new Place(id_lieu, nom, rue, ville, pays, zip_code, longitude, latitude);
 
                hashmapPlace.put(id_lieu, place);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
    public void addGeoTag(){
       
        try {
            Long id_geo;
            String date;
            Long id_place;
            Long id_tagger;
            Long id_friend;

            leResultat = transmission.executeQuery("select * from geolocalisation");
            
            while (leResultat.next())
            {  
                id_geo = leResultat.getLong("id_geo");
                date = leResultat.getString("create_time");
                id_place = leResultat.getLong("id_lieu");
                id_tagger = leResultat.getLong("id_user_geo") ;
          
                GeoTag geotag = new GeoTag(id_geo, date, hashmapPlace.get(id_place), hashmapUsers.get(id_tagger));
                hashmapUsers.get(id_tagger).addHasTagged(geotag);
 
                hashmapGeoTag.put(id_geo, geotag);
            }
            
            
            leResultat = transmission.executeQuery("select * from tagger");
            
             while (leResultat.next())
            {  
                id_geo = leResultat.getLong("id_geo");
                id_friend = leResultat.getLong("id_friend");
            
                hashmapUsers.get(id_friend).addHasBeenTagged(hashmapGeoTag.get(id_geo));
                hashmapGeoTag.get(id_geo).addUserTagged(hashmapUsers.get(id_friend));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    

    public void addLikes()
    {
        try {
            Long id_user;
            Long id_like;
            String nom;
            String categorie;
            
            leResultat = transmission.executeQuery("SELECT * FROM `like`");
            
            while (leResultat.next())
            {  
                id_like = leResultat.getLong("id_like");
                nom = leResultat.getString("nom");
                categorie = leResultat.getString("categorie");
               
                Like like = new Like(id_like, nom, categorie);
 
                hashmapLike.put(id_like, like);
            }
            
            
            leResultat = transmission.executeQuery("select * from aime");
            
            while (leResultat.next())
            {  
                id_user = leResultat.getLong("id_user");
                id_like = leResultat.getLong("id_like");
            
                hashmapUsers.get(id_user).addLike(hashmapLike.get(id_like));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(LoadGraph.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    

    public void fillGraph()
    {
        
        hashmapUsers = new HashMap<Long, User>();
        hashmapGroups = new HashMap<Long, Group>();
        hashmapLocation = new HashMap<Long, Location>();
        hashmapEvent = new HashMap<Long, Event>();
        hashmapWork = new HashMap<Long, Work>();
        hashmapLike = new HashMap<Long, Like>();
        
        //ordre des appels important
        connect();
        addLocation();  //location en premier car il faut remplacer dans users l'id par l'objet Location
        addUsers();
        addWork();
        addFriendLink();
        addGroupLink(); 
        addEventLink();
        addSchoolYear();
        addSchoolYear();
        addLikes();

    }

    public static HashMap<Long, User> getHashmapUsers() {
        return hashmapUsers;
    }
}
